<?php

class main
{
    private $conn;

    function __construct()
    {
        $dsn = "mysql:host=localhost;dbname=library";
        try {
            $this->conn = new PDO($dsn, 'root', '');
            $this->conn->query('set names utf8');
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);//捕获错误
        } catch (PDOException $e) {
            exit("系统错误:" . $e->getMessage());
        }
    }

    function __destruct()
    {
        $this->conn = null;
    }

    function IndexRank()
    {
        try {
            $sql = "SELECT * FROM (SELECT id,COUNT(id) num,bookid FROM jie  GROUP BY bookid) as q JOIN (
SELECT
	a.*, b.type,
	c. chuban,
	d. name
FROM
	bookinfo AS a
JOIN booktype AS b ON a.typeid = b.id
JOIN isbn AS c ON a.ISBN = c.ISBN
JOIN bookcase AS d ON a.bookcase = d.id
) as w ON q.bookid = w.id ORDER BY q.num DESC
";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function readertype()
    {
        try {
            $sql = "select * from readertype";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function reader()
    {
        try {
            $sql = "SELECT a.*,b.name as typename from reader as a left JOIN readertype as b ON a.type=b.id";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function userinfo($id)
    {
        try {
            $sql = "SELECT a.*,b.name as typename from reader as a left JOIN readertype as b ON a.type=b.id where a.id = '$id'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function del($tab, $c1, $c2)
    {
        try {
            $sql = "delete from $tab where $c1 = '$c2'";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function admin()
    {
        try {
            $sql = "SELECT * FROM admin a LEFT JOIN quanxian b on a.id=b.id";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function getAdmin($id)
    {
        try {
            $sql = "SELECT * FROM admin a LEFT JOIN quanxian b on a.id=b.id where a.id = '$id'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addAdmin($id, $a, $b, $c, $d, $e)
    {
        try {
            $sql = "update quanxian set system =?,readerset=?,bookset=?,book=?,syschaxun=? where id = ?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $id));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function jieyue()
    {
        try {
            $sql = "SELECT a.*,b.barcode,b.bookname,c.barcode ma,c.`name` FROM jie a LEFT JOIN bookinfo b ON a.bookid = b.id JOIN reader c ON a.readerid = c.id where a.ifback = 1";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function shujia()
    {
        try {
            $sql = "SELECT * from bookcase";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function booktype()
    {
        try {
            $sql = "SELECT * from booktype";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function chubanshe()
    {
        try {
            $sql = "SELECT * from isbn";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addbookinfo($a, $b, $c, $d, $e, $f, $g, $h, $i, $k, $j)
    {
        try {
            $sql = "insert into bookinfo (barcode,bookname,typeid,author,fanyi,ISBN,price,page,bookcase,storage,intime) values (?,?,?,?,?,?,?,?,?,?,?)";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $f, $g, $h, $i,$k,$j));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function adduserinfo($a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $k)
    {
        try {
            $sql = "insert into reader (barcode,name,sex,work,birthday,zhenjian,idcard,phone,email,createdata,type) values (?,?,?,?,?,?,?,?,?,?,?)";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $k));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function xiugaiuserinfo($a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $k)
    {
        try {
            $sql = "update reader set barcode =?,name =?,sex=?,work=?,birthday=?,zhenjian=?,idcard=?,phone=?,email=?,type=? where id= ?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $f, $g, $h, $i, $j, $k));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function xiugaibookinfo($a, $b, $c, $d, $e, $f, $g, $h, $i,$k ,$j)
    {
        try {
            $sql = "update bookinfo set barcode =?,bookname =?,typeid=?,author=?,fanyi=?,ISBN=?,price=?,page=?,bookcase=?,storage=? where id= ?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $f, $g, $h, $i, $k,$j));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addshujia($shujia)
    {
        try {
            $sql = "insert into bookcase (name) values (?)";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($shujia));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addreadertype($type, $number)
    {
        try {
            $sql = "insert into readertype (name,number) values (?,?)";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($type, $number));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addadminer($name, $pw)
    {
        try {
            $sql = "insert into admin (name,pw) values (?,?)";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($name, md5($pw)));
            return $this->conn->lastInsertId();
        } catch (Exception $e) {
            return $e;
        }
    }

    function addadminers($id)
    {
        try {
            $sql = "insert into quanxian (id,system,readerset,bookset,book,syschaxun) values ($id,1,1,1,1,1)";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function bookdangan()
    {
        try {
            $sql = "SELECT a.*,b.type,c.chuban,d.name from bookinfo a LEFT JOIN booktype b ON a.typeid = b.id  left JOIN isbn c ON a.ISBN = c.ISBN left JOIN bookcase d ON a.bookcase = d.id";
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function bookinfo($id)
    {
        try {
            $sql = "SELECT a.*,b.type,c.chuban,d.name from bookinfo a LEFT JOIN booktype b ON a.typeid = b.id  left JOIN isbn c ON a.ISBN = c.ISBN left JOIN bookcase d ON a.bookcase = d.id where a.id = '$id'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function libraryinfo()
    {
        try {
            $sql = "SELECT * from library";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function xiugailibraryinfo($a, $b, $c, $d, $e, $f, $g, $h)
    {
        try {
            $sql = "update library set name =?,boss =?,phone=?,dizhi=?,mail=?,url=?,careatdate=?,jianjie=?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $b, $c, $d, $e, $f, $g, $h));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function danganchaxun($a, $value)
    {
        try {
            switch ($a) {
                case '0' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where a.barcode = '$value' ";
                    break;
                case '1' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where b.type    like '%$value%' ";
                    break;
                case '2' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where a.bookname like '%$value%' ";
                    break;
                case '3' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where a.author like '%$value%' ";
                    break;
                case '4' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where c.chuban like '%$value%' ";
                    break;
                case '5' :
                    $sql = "SELECT a.*, b.type, c.chuban, d. NAME FROM bookinfo a LEFT JOIN booktype b ON a.typeid = b.id LEFT JOIN isbn c ON a.ISBN = c.ISBN LEFT JOIN bookcase d ON a.bookcase = d.id where d.name like '%$value%' ";
                    break;
            }
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $value));
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function bookback($id)
    {
        try {
            $sql = "SELECT
	*
FROM
	(
		SELECT
			a.*, b. NAME AS typename,
			b.number
		FROM
			reader AS a
		LEFT JOIN readertype AS b ON a.type = b.id
		WHERE
			a.barcode = '$id'
	) A
LEFT JOIN (
	SELECT
		a.barcode,COUNT(1) numbers
	FROM
		reader AS a
	LEFT JOIN readertype AS b ON a.type = b.id
	LEFT JOIN jie ON a.id = jie.readerid
	WHERE
		a.barcode = '$id'
	AND ifback = 1
	GROUP BY
		a.id
) B
ON A.barcode = B.barcode";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function getbookback($id,$pageNum,$pageSize,$t)
    {
        try {
            if ($t){//1
                $sql = "SELECT *,a.id as backid,a.readerid,a.bookid,a.id ID,a.jietime,a.backtime,a.ifback,e.name shujia FROM jie a 
                    LEFT JOIN reader b on a.readerid = b.id 
                    LEFT JOIN bookinfo c ON a.bookid = c.id 
                    LEFT JOIN isbn d ON c.ISBN = d.ISBN 
                    LEFT JOIN bookcase e ON c.bookcase = e.id WHERE b.barcode = '$id' and a.ifback = 1 limit ".($pageNum-1)*$pageSize.",".$pageSize;
            }
            else{
                $sql = "SELECT *,a.id as backid,a.readerid,a.bookid,a.id ID,a.jietime,a.backtime,a.ifback,e.name shujia FROM jie a 
                    LEFT JOIN reader b on a.readerid = b.id 
                    LEFT JOIN bookinfo c ON a.bookid = c.id 
                    LEFT JOIN isbn d ON c.ISBN = d.ISBN 
                    LEFT JOIN bookcase e ON c.bookcase = e.id WHERE b.barcode = '$id' and a.ifback = 1";
            }
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function modifabackbook($id, $bookid)
    {
        try {
            $time = time();
            $sql = "update jie,bookinfo set ifback = 0,storage = storage + 1,backtime=$time where jie.id = '$id' and bookinfo.id = '$bookid'";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function jieyuebook($search, $value)
    {
        try {
            if ($search == 1) {
                $sql = "SELECT * FROM bookinfo a JOIN isbn b on a.ISBN = b.ISBN JOIN bookcase c ON a.bookcase = c.id WHERE a.barcode = '$value'";
            } else {
                $sql = "SELECT * FROM bookinfo a JOIN isbn b on a.ISBN = b.ISBN JOIN bookcase c ON a.bookcase = c.id WHERE a.bookname = '$value'";
            }
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function insertbook($userid, $bookid)
    {
        try {
            $time = time();
            $backtime = strtotime('+1 month');
            $sql = "insert into jie (readerid,bookid,jietime,backtime) values ('$userid','$bookid','$time','$backtime')";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function jie($pageNum,$pageSize,$a,$jietime,$backtime)
    {
        try {
            if ($a == 1){
                $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id ORDER BY a.jietime,a.backtime limit ".($pageNum-1)*$pageSize.",".$pageSize;
            }
            elseif ($a == 2){
                $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id where jietime > '$jietime' and backtime < '$backtime' ORDER BY a.jietime,a.backtime limit ".($pageNum-1)*$pageSize.",".$pageSize;
            }
            elseif ($a == 3){
                $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id where jietime > '$jietime' and backtime < '$backtime' ORDER BY a.jietime,a.backtime";
            }
            else{
                $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id ORDER BY a.jietime,a.backtime";
            }
            $sth = $this->conn->query($sql);
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function xujie($id)
    {
        try {
            $sql = "update jie  set backtime = backtime + 2592000  where id = '$id'";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function jieyuechaxun($a, $value,$tiaojian,$pageNum,$pageSize)
    {
        try {
            if ($tiaojian){//得到数据
                switch ($a) {
                    case '0' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.barcode = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize;
                        break;
                    case '1' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where d.type = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize;
                        break;
                    case '2' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.bookname = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize;
                        break;
                    case '3' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.author = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize;
                        break;
                    case '4' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where e.chuban = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize;
                        break;
                    case '5' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where bookcase.`name` = '$value' limit ".($pageNum-1)*$pageSize.",".$pageSize ;
                        break;
                }
            }else{//得到总数
                switch ($a) {
                    case '0' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.barcode = '$value' ";
                        break;
                    case '1' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where d.type = '$value' ";
                        break;
                    case '2' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.bookname = '$value' ";
                        break;
                    case '3' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where c.author = '$value' ";
                        break;
                    case '4' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where e.chuban = '$value' ";
                        break;
                    case '5' :
                        $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where bookcase.`name` = '$value' ";
                        break;
                }
            }

            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $value));
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function jieyuechaxunandtime($a, $value, $jietime, $backtime)
    {
        try {
            switch ($a) {
                case '0' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  c.barcode = '$value'and jietime > '$jietime' and backtime < '$backtime'  ";
                    break;
                case '1' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  d.type    like '%$value%'and jietime > '$jietime' and backtime < '$backtime'  ";
                    break;
                case '2' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  c.bookname like '%$value%'and jietime > '$jietime' and backtime < '$backtime'  ";
                    break;
                case '3' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  c.author like '%$value%'and jietime > '$jietime' and backtime < '$backtime'  ";
                    break;
                case '4' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  e.chuban like '%$value%' and jietime > '$jietime' and backtime < '$backtime' ";
                    break;
                case '5' :
                    $sql = "SELECT a.*,b.barcode,b.name user,c.barcode bookcode,c.bookname,c.typeid,c.author,c.ISBN,c.bookcase,d.type,e.chuban,bookcase.name shujia  FROM jie a JOIN reader b ON a.readerid = b.id JOIN bookinfo c on a.bookid = c.id JOIN bookcase ON c.bookcase = bookcase.id JOIN booktype d ON  c.typeid = d.id JOIN isbn e ON c.ISBN = e.ISBN where  bookcase.`name` like '%$value%'and jietime > '$jietime' and backtime < '$backtime'  ";
                    break;
            }
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a, $value));
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }

    function login($username, $pw)
    {
        try {
            $sql = "select * from admin where name = ? and pw = ?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($username, md5($pw)));
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function checkAdmin($name)
    {
        try {
            $sql = "SELECT * FROM admin LEFT JOIN quanxian ON admin.id = quanxian.id WHERE admin.`name` = '$name'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (Exception $e) {
            return $e;
        }
    }

    function updatekucun($bookid)
    {
        try {
            $sql = "update bookinfo set storage = storage - 1  where id = '$bookid'";
            $sth = $this->conn->query($sql);
            return $sth->rowCount();
        } catch (Exception $e) {
            return $e;
        }
    }

    function chaxunbook($a)
    {
        try {
            $sql = "select a.barcode,a.bookname,a.author,b.chuban,a.ISBN from bookinfo a LEFT JOIN isbn b ON a.ISBN = b.ISBN where a.bookname= ?";
            $sth = $this->conn->prepare($sql);
            $sth->execute(array($a));
            return $sth->fetchAll();
        } catch (Exception $e) {
            return $e;
        }
    }
    function  checkbacode($code){
        try {
            $sql = "SELECT * FROM bookinfo where barcode = '$code'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (PDOException $e) {
            return $e;
        }
    }
    function  checkusercode($code){
        try {
            $sql = "SELECT * FROM reader where barcode = '$code'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (PDOException $e) {
            return $e;
        }
    }
    function  checkname($name){
        try {
            $sql = "SELECT * FROM admin where name = '$name'";
            $sth = $this->conn->query($sql);
            return $sth->fetch();
        } catch (PDOException $e) {
            return $e;
        }
    }
}
